package com.softer.chengxin.outside.dao.provider;

import com.softer.chengxin.common.domin.Page;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author wangixnrui
 * @date 2018/10/22
 */
public class OutsideDaoProvider {

    /**
     * 获取企业年度综合评价
     */
    public String getCorpRecord(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        Integer qyId = (Integer) map.get("qyId");
        Page page = (Page) map.get("page");
        String year = (String) map.get("year");
        String dj = (String) map.get("dj");
        String qyName = (String) map.get("qyName");

        sql.append("select s.`name` as streetName,c.id as id,c.`name` as `name`,c.business_license_no as businessLicenseNo,r.dj as dj,r.`year` as `year`");
        sql.append(" from company_base_info c left join score_record r on c.id=r.poguid left join district_street s on c.district=s.id");
        sql.append(" where c.state=2 ");
        if (qyId!=null){
            sql.append(" and c.id=").append(qyId);
        }
        if (StringUtils.isNotBlank(year)){
            sql.append(" and r.`year`=").append(year);
        }
        if(StringUtils.isNotBlank(dj)){
            sql.append(" and r.dj=#{dj}");
        }
        if(StringUtils.isNotBlank(qyName)){
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        sql.append(" order by c.gmt_create desc");
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }
    /**
     * 获取企业年度综合评价  条数
     */
    public String getCorpRecordCount(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        Integer qyId = (Integer) map.get("qyId");
        String year = (String) map.get("year");
        String dj = (String) map.get("dj");
        String qyName = (String) map.get("qyName");

        sql.append("select count(*)");
        sql.append(" from company_base_info c left join score_record r on c.id=r.poguid left join district_street s on c.district=s.id");
        sql.append(" where c.state=2 ");
        if (qyId!=null){
            sql.append(" and c.id=").append(qyId);
        }
        if (StringUtils.isNotBlank(year)){
            sql.append(" and r.`year`=").append(year);
        }
        if(StringUtils.isNotBlank(dj)){
            sql.append(" and r.dj=#{dj}");
        }
        if(StringUtils.isNotBlank(qyName)){
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        return sql.toString();
    }

    /**
     * 查询企业优良/负面信息列表
     */
    public String getCorpScoreDetail(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        Integer infoType = (Integer) map.get("infoType");
        Integer qyId = (Integer) map.get("qyId");
        String dj = (String) map.get("dj");
        Page page = (Page) map.get("page");
        String qyName = (String) map.get("qyName");

        sql.append("select d.id as id,d.state as state,d.corp_id as corpId,c.`name` as qyName,d.adder as adder,d.approver as approver,d.reason as reason,d.score as score,d.bjdw as bjdw,date(d.effective_begin_time) as effectiveBeginTime,date(DATE_FORMAT(d.effective_end_time, '%Y-%m-%d')) as effectiveEndTime");
        sql.append(" from score_detail d,company_base_info c ");
        sql.append(" where d.corp_id=c.id");
        sql.append(" and d.effective_end_time >= now()");
//        if(infoType == 1){
//            sql.append("  and d.effective_end_time <= DATE_ADD(NOW(),INTERVAL 5 YEAR)");
//        }else if (infoType == 2){
//            sql.append("  and d.effective_end_time <= DATE_ADD(NOW(),INTERVAL 3 YEAR)");
//        }
        sql.append(" and d.effective_begin_time<=now()");
        if (infoType!=null){
            sql.append(" and d.info_type=").append(infoType);
        }
        if(StringUtils.isNotBlank(dj)){
            sql.append(" and d.corp_id in (select poguid from score_record r where r.`year`=#{year} and r.dj='").append(dj).append("')");
        }
        if(qyId!=null){
            sql.append(" and d.corp_id=#{qyId}");
        }
        if(StringUtils.isNotBlank(qyName)){
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%') ");
        }

        sql.append(" order by d.gmt_create desc");
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }
    /**
     * 查询企业优良/负面信息列表 条数
     */
    public String getCorpScoreDetailCount(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        Integer infoType = (Integer) map.get("infoType");
        Integer qyId = (Integer) map.get("qyId");
        String dj = (String) map.get("dj");
        String qyName = (String) map.get("qyName");

        sql.append("select count(*)");
        sql.append(" from score_detail d,company_base_info c ");
        sql.append(" where d.corp_id=c.id");
        sql.append(" and d.effective_end_time >= now()");
        sql.append(" and d.effective_begin_time<=now()");
        if (infoType!=null){
            sql.append(" and d.info_type=").append(infoType);
        }
        if(StringUtils.isNotBlank(dj)){
            sql.append(" and d.corp_id in (select poguid from score_record r where r.`year`=#{year} and r.dj='").append(dj).append("')");
        }
        if(qyId!=null){
            sql.append(" and d.corp_id=#{qyId}");
        }
        if(StringUtils.isNotBlank(qyName)){
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%') ");
        }
        return sql.toString();
    }

    /**
     * 根据企业名和等级查询企业信息
     */
    public String getCorpList(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String qyName = (String) map.get("qyName");
        String dj = (String) map.get("dj");
        Page page = (Page) map.get("page");

        sql.append("select c.id as id,c.`name` as name,c.legal_person as legalPerson,r.dj as dj,d.`name` as districtName ");
        sql.append(" from company_base_info c left join district_street d on c.district=d.id,score_record r ");
        sql.append(" where c.id=r.poguid and c.state=2");
        if (StringUtils.isNotBlank(qyName)){
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(dj)){
            sql.append(" and r.dj=#{dj}");
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 根据企业名和等级查询企业信息 条数
     */
    public String getCorpListCount(HashMap<String,Object> map){
        StringBuffer sql = new StringBuffer(20);
        String qyName = (String) map.get("qyName");
        String dj = (String) map.get("dj");

        sql.append("select count(*) ");
        sql.append(" from company_base_info c left join district_street d on c.district=d.id,score_record r ");
        sql.append(" where c.id=r.poguid and c.state=2");
        if (StringUtils.isNotBlank(qyName)){
            sql.append(" and c.`name` like CONCAT('%',#{qyName},'%')");
        }
        if (StringUtils.isNotBlank(dj)){
            sql.append(" and r.dj=#{dj}");
        }

        return sql.toString();
    }

}
